package cn.burnit.service;

import cn.burnit.pojo.Book;
import cn.burnit.utils.ConnectUtils;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * 图书jdbc方法
 */
public class BookService {
    Connection connection=null;

    /**
     * 添加书籍
     * @param book
     * @return
     */
    public int addBook(Book book) {
        ConnectUtils.getDriver();

        int i = 0;
        try {
            connection=ConnectUtils.getConnection();
            String addSql="insert into book values(?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement ps = connection.prepareStatement(addSql);
            ps.setString(1,book.getId());
            ps.setString(2,book.getBookname());
            ps.setString(3,book.getBooktype());
            ps.setString(4,book.getAuthor());
            ps.setString(5,book.getTranslator());
            ps.setString(6,book.getPublisher());
            ps.setDate(7, (Date) book.getPublish_time());
            ps.setFloat(8,book.getPricce());
            ps.setInt(9,book.getStock());
            ps.setInt(10,book.getOutloan());
            i = ps.executeUpdate();
            if (i>0){
                System.out.println("添加成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    /**
     *通过id查询数据
     * @param id
     * @return
     */
    public Book getBookById(String id) {
        ConnectUtils.getDriver();

        try {
            connection=ConnectUtils.getConnection();
            String selById="select * from book where id=?";
            PreparedStatement ps = connection.prepareStatement(selById);
            ps.setString(1,id);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()){
                Book book = new Book();
                String bid = resultSet.getString(1);
                String bookName=resultSet.getString(2);
                String bookType=resultSet.getString(3);
                String author=resultSet.getString(4);
                String translator=resultSet.getString(5);
                String publisher=resultSet.getString(6);
                String strTime= resultSet.getString(7);
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                java.util.Date publishTime = simpleDateFormat.parse(strTime);
                float price= Float.parseFloat(resultSet.getString(8));
                int stock=resultSet.getInt(9);
                int outloan=resultSet.getInt(10);
                book.setId(bid);
                book.setBookname(bookName);
                book.setBooktype(bookType);
                book.setAuthor(author);
                book.setTranslator(translator);
                book.setPublisher(publisher);
                book.setPublish_time(publishTime);
                book.setPricce(price);
                book.setStock(stock);
                book.setOutloan(outloan);
                return book;
            }
        } catch (SQLException | ParseException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 修改书籍
     * @param book
     * @return
     */
    public int updateBook(Book book) {
        int i=0;
        ConnectUtils.getDriver();

        try {
            connection=ConnectUtils.getConnection();
            String updateSql="update book set bookname=? , booktype=? , author=? , translator=? , publisher=? , publish_time=? , price=? , stock=? where id=? ";
            PreparedStatement ps = connection.prepareStatement(updateSql);
            ps.setString(1,book.getBookname());
            ps.setString(2,book.getBooktype());
            ps.setString(3,book.getAuthor());
            ps.setString(4,book.getTranslator());
            ps.setString(5,book.getPublisher());
            ps.setDate(6, (Date) book.getPublish_time());
            ps.setFloat(7,book.getPricce());
            ps.setInt(8,book.getStock());
            ps.setString(9,book.getId());
            i=ps.executeUpdate();
            if (i>0){
                System.out.println("更新成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    /**
     * 更新库存
     * @param book
     * @return
     */
    public int updateBookStockAndOutloan(Book book) {
        int i=0;
        ConnectUtils.getDriver();

        try {
            connection=ConnectUtils.getConnection();
            String updateSql="update book set stock=?,outloan=? where id=? ";
            PreparedStatement ps = connection.prepareStatement(updateSql);
            ps.setInt(1,book.getStock());
            ps.setInt(2,book.getOutloan());
            ps.setString(3,book.getId());
            i=ps.executeUpdate();
            if (i>0){
                System.out.println("库存量和借书量更新成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    /**
     * 通过书籍查询指定的书
     * @param bookame
     * @return
     */
    public Book findBookByName(String bookame) {
       // Book book = null;
        ConnectUtils.getDriver();

        try {
            connection=ConnectUtils.getConnection();
            String findSql="select * from book where bookname=?";
            PreparedStatement ps = connection.prepareStatement(findSql);
            ResultSet rs= ps.executeQuery();
            while (rs.next()){
                Book book = new Book();
                bookame=rs.getString(1);
                book.setBookname(bookame);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 通过书名查找所有
     * @param bookname
     * @return
     */
    public List<Book> listBook(String bookname){
        ConnectUtils.getDriver();
        Book book=null;
        List<Book> list = new ArrayList<>();
        try {
            connection=ConnectUtils.getConnection();
            String listBookSql="select * from book where bookname like ?";
            PreparedStatement ps = connection.prepareStatement(listBookSql);
            ps.setString(1,"%"+bookname+"%");
            ResultSet result = ps.executeQuery();
            while (result.next()){
                String id = result.getString("id");
                String bookname1 = result.getString("bookname");
                String booktype = result.getString("booktype");
                String author = result.getString("author");
                String translator = result.getString("translator");
                String publisher = result.getString("publisher");
                Date publish_time = result.getDate("publish_time");
                float price = result.getFloat("price");
                int stock = result.getInt("stock");
                book=new Book(id,bookname1,booktype,author,translator,publisher,publish_time,price,stock);
                list.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }


    /**
     * 通过id删除
     * @param checkId
     * @return
     */
    public int delById(String checkId) {
        int i=0;
        ConnectUtils.getDriver();
        try {
            connection=ConnectUtils.getConnection();
            String delSql="delete from book where id=?";
            PreparedStatement ps = connection.prepareStatement(delSql);
            ps.setString(1,checkId);
            i=ps.executeUpdate();
            if (i>0){
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    public int updateBookStock(Book book) {
        int i=0;
        ConnectUtils.getDriver();

        try {
            connection=ConnectUtils.getConnection();
            String updateStockSql="update book set stock=? where id=? ";
            PreparedStatement ps = connection.prepareStatement(updateStockSql);
            ps.setInt(1,book.getStock());
            ps.setString(2,book.getId());
            i=ps.executeUpdate();
            if (i>0){
                System.out.println("库存量和借书量更新成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }
}
